package com.usth.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import sun.java2d.pipe.SpanShapeRenderer.Simple;

import com.bcd.tools.DBUtils;
import com.usth.model.Airport;
import com.usth.model.BuyTVO;
import com.usth.model.FlightCom;
import com.usth.model.FlightInfo;
import com.usth.model.FlightStopTime;
import com.usth.model.FlightVO;
import com.usth.model.SearchVO;
import com.usth.model.Tickets;
import com.usth.model.TransitPrice;

public class SearchDaoImpl extends HibernateDaoSupport implements ISearchDao {

	public List<FlightVO> search(SearchVO search) throws Exception {
		String hql="from FlightInfo where takeOffAirport in (select airportId from  Airport where airportCity=?) and landingAirport in (select airportId from  Airport where airportCity=?)  ";
		String hql1="from FlightInfo where takeOffAirport in (select airportId from  Airport where airportCity=?)";
		String hql2="from Airport where airportCity=? and airportId=?";
String hql3="from FlightStopTime where flightId=? and stopBeginTime>?";
		List<FlightInfo> temp= this.getHibernateTemplate().find(hql1,search.getStart_city());
		List<FlightInfo> list= this.getHibernateTemplate().find(hql,new String[]{search.getStart_city(),search.getEnd_city()});
		if(temp!=null&&temp.size()!=0){
			Connection con=DBUtils.getconnection();
			for(FlightInfo f:temp){
				if(f.getTransitAirport()!=null){
					String[] tran=f.getTransitAirport().split(",");
					for(String s:tran){
						List li=this.getHibernateTemplate().find(hql2,new Object[]{search.getEnd_city(),Integer.parseInt(s)});
						if(li!=null&&li.size()!=0){
							Airport airp=(Airport) li.get(0);
							PreparedStatement pst=con.prepareStatement("select price from transit_price where airport_id=? and flight_id=?");
							pst.setInt(1, airp.getAirportId());
							pst.setString(2, f.getFlightId());
							ResultSet rs=pst.executeQuery();
							Double d=null;
							while(rs.next()){
								 d=rs.getDouble(1);
							}
							f.setTotalPrice(d);
							f.setLandingAirport(airp.getAirportId()+"");
							rs.close();
							pst.close();
							list.add(f);
							continue;
						}
					}
				}
			}
			DBUtils.close(con);
		}
Date d=new Date();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
List lis=new ArrayList();
		for(FlightInfo f1:list){
			List<FlightStopTime> li=this.getHibernateTemplate().find(hql3,new Object[]{f1.getFlightId(),sdf.format(d)});
			for(FlightStopTime fs:li){
				Date cd=null;
				Date bd=null;
				Date ed=null;
				try {
					cd = sdf.parse(search.getStart_date());
					bd = sdf.parse(fs.getStopBeginTime());
					ed = sdf.parse(fs.getStopEndTime());
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if((cd.before(ed)&&cd.after(bd))||cd.equals(bd)||cd.equals(ed)){
//					list.remove(f1);
					lis.add(f1);
				}
			}
			
		}
		list.removeAll(lis);
		List<FlightVO> flist=new ArrayList<FlightVO>();
		for(FlightInfo f2:list){
			Airport a=(Airport) this.getHibernateTemplate().get("com.usth.model.Airport", Integer.parseInt(f2.getTakeOffAirport()));
			Airport b=(Airport) this.getHibernateTemplate().get("com.usth.model.Airport", Integer.parseInt(f2.getLandingAirport()));
			FlightVO f=new FlightVO(f2.getFlightId(), a.getAirportName(), b.getAirportName(), f2.getTakeOffTime(), f2.getLandingTime(), f2.getTotalPrice()+"");
			flist.add(f);
		}
		return flist;
		
	}

	public void searchD(SearchVO search) {
		HttpSession session =ServletActionContext.getRequest().getSession();
		String hql="from FlightInfo where takeOffAirport in (select airportId from  Airport where airportCity=?) and landingAirport in (select airportId from  Airport where airportCity=?)  ";
		String hql1="from FlightInfo where takeOffAirport in (select airportId from  Airport where airportCity=?)";
		String hql2="from Airport where airportCity=? and airportId=?";
String hql3="from FlightStopTime where flightId=? and stopBeginTime>?";
		List<FlightInfo> temp= this.getHibernateTemplate().find(hql1,search.getEnd_city());
		List<FlightInfo> list= this.getHibernateTemplate().find(hql,new String[]{search.getEnd_city(),search.getStart_city()});
		if(temp!=null&&temp.size()!=0){
			Connection con=DBUtils.getconnection();
			for(FlightInfo f:temp){
				if(f.getTransitAirport()!=null){
					String[] tran=f.getTransitAirport().split(",");
					for(String s:tran){
						List li=this.getHibernateTemplate().find(hql2,new Object[]{search.getStart_city(),Integer.parseInt(s)});
						try {
							if(li!=null&&li.size()!=0){
								Airport airp=(Airport) li.get(0);
								PreparedStatement pst=con.prepareStatement("select price from transit_price where airport_id=? and flight_id=?");
								pst.setInt(1, airp.getAirportId());
								pst.setString(2, f.getFlightId());
								ResultSet rs=pst.executeQuery();
								Double d=null;
								while(rs.next()){
									 d=rs.getDouble(1);
								}
								f.setTotalPrice(d);
								f.setLandingAirport(airp.getAirportId()+"");
								rs.close();
								pst.close();
								list.add(f);
								continue;
							}
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
			DBUtils.close(con);
		}
Date d=new Date();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		for(FlightInfo f1:list){
			List<FlightStopTime> li=this.getHibernateTemplate().find(hql3,new Object[]{f1.getFlightId(),sdf.format(d)});
			for(FlightStopTime fs:li){
				Date cd=null;
				Date bd=null;
				Date ed=null;
				try {
					cd = sdf.parse(search.getReturn_date());
					bd = sdf.parse(fs.getStopBeginTime());
					ed = sdf.parse(fs.getStopEndTime());
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if((cd.before(ed)&&cd.after(bd))||cd.equals(bd)||cd.equals(ed)){
					list.remove(f1);
				}
			}
			
		}
		List<FlightVO> flist=new ArrayList<FlightVO>();
		for(FlightInfo f2:list){
			Airport a=(Airport) this.getHibernateTemplate().get("com.usth.model.Airport", Integer.parseInt(f2.getTakeOffAirport()));
			Airport b=(Airport) this.getHibernateTemplate().get("com.usth.model.Airport", Integer.parseInt(f2.getLandingAirport()));
			FlightVO f=new FlightVO(f2.getFlightId(), a.getAirportName(), b.getAirportName(), f2.getTakeOffTime(), f2.getLandingTime(), f2.getTotalPrice()+"");
			flist.add(f);
		}
		session.setAttribute("flightsend", flist);
		DBUtils.page(1,"flightsend", "flight_e");
	}

	public void searchM(SearchVO search) {
		HttpSession session =ServletActionContext.getRequest().getSession();
		List<FlightVO> list_fst=this.searchM_fst(search);
		session.setAttribute("flights", list_fst);
		DBUtils.page(1,"flights", "flight_s");
		List<FlightVO> list_scd=this.searchM_scd(search);
		session.setAttribute("flightsend", list_scd);
		DBUtils.page(1,"flightsend", "flight_e");
	}
	
	private List<FlightVO> searchM_scd(SearchVO search){
		HttpSession session =ServletActionContext.getRequest().getSession();
		String hql="from FlightInfo where takeOffAirport in (select airportId from  Airport where airportCity=?) and landingAirport in (select airportId from  Airport where airportCity=?)  ";
		String hql1="from FlightInfo where takeOffAirport in (select airportId from  Airport where airportCity=?)";
		String hql2="from Airport where airportCity=? and airportId=?";
String hql3="from FlightStopTime where flightId=? and stopBeginTime>?";
		List<FlightInfo> temp= this.getHibernateTemplate().find(hql1,search.getTransit_city());
		List<FlightInfo> list= this.getHibernateTemplate().find(hql,new String[]{search.getTransit_city(),search.getEnd_city()});
		if(temp!=null&&temp.size()!=0){
			Connection con=DBUtils.getconnection();
			for(FlightInfo f:temp){
				if(f.getTransitAirport()!=null){
					String[] tran=f.getTransitAirport().split(",");
					for(String s:tran){
						List li=this.getHibernateTemplate().find(hql2,new Object[]{search.getEnd_city(),Integer.parseInt(s)});
						try {
							if(li!=null&&li.size()!=0){
								Airport airp=(Airport) li.get(0);
								PreparedStatement pst=con.prepareStatement("select price from transit_price where airport_id=? and flight_id=?");
								pst.setInt(1, airp.getAirportId());
								pst.setString(2, f.getFlightId());
								ResultSet rs=pst.executeQuery();
								Double d=null;
								while(rs.next()){
									 d=rs.getDouble(1);
								}
								f.setTotalPrice(d);
								f.setLandingAirport(airp.getAirportId()+"");
								rs.close();
								pst.close();
								list.add(f);
								continue;
							}
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
			DBUtils.close(con);
		}
Date d=new Date();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		for(FlightInfo f1:list){
			List<FlightStopTime> li=this.getHibernateTemplate().find(hql3,new Object[]{f1.getFlightId(),sdf.format(d)});
			for(FlightStopTime fs:li){
				Date cd=null;
				Date bd=null;
				Date ed=null;
				try {
					cd = sdf.parse(search.getSecond_start_date());
					bd = sdf.parse(fs.getStopBeginTime());
					ed = sdf.parse(fs.getStopEndTime());
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if((cd.before(ed)&&cd.after(bd))||cd.equals(bd)||cd.equals(ed)){
					list.remove(f1);
				}
			}
			
		}
		List<FlightVO> flist=new ArrayList<FlightVO>();
		for(FlightInfo f2:list){
			Airport a=(Airport) this.getHibernateTemplate().get("com.usth.model.Airport", Integer.parseInt(f2.getTakeOffAirport()));
			Airport b=(Airport) this.getHibernateTemplate().get("com.usth.model.Airport", Integer.parseInt(f2.getLandingAirport()));
			FlightVO f=new FlightVO(f2.getFlightId(), a.getAirportName(), b.getAirportName(), f2.getTakeOffTime(), f2.getLandingTime(), f2.getTotalPrice()+"");
			flist.add(f);
		}
		return flist;
//		session.setAttribute("flightsend", flist);
//		DBUtils.page(1,"flightsend", "flight_e");
	}

	private List<FlightVO> searchM_fst(SearchVO search){
		HttpSession session =ServletActionContext.getRequest().getSession();
		String hql="from FlightInfo where takeOffAirport in (select airportId from  Airport where airportCity=?) and landingAirport in (select airportId from  Airport where airportCity=?)  ";
		String hql1="from FlightInfo where takeOffAirport in (select airportId from  Airport where airportCity=?)";
		String hql2="from Airport where airportCity=? and airportId=?";
String hql3="from FlightStopTime where flightId=? and stopBeginTime>?";
		List<FlightInfo> temp= this.getHibernateTemplate().find(hql1,search.getStart_city());
		List<FlightInfo> list= this.getHibernateTemplate().find(hql,new String[]{search.getStart_city(),search.getTransit_city()});
		if(temp!=null&&temp.size()!=0){
			Connection con=DBUtils.getconnection();
			for(FlightInfo f:temp){
				if(f.getTransitAirport()!=null){
					String[] tran=f.getTransitAirport().split(",");
					for(String s:tran){
						List li=this.getHibernateTemplate().find(hql2,new Object[]{search.getTransit_city(),Integer.parseInt(s)});
						try {
							if(li!=null&&li.size()!=0){
								Airport airp=(Airport) li.get(0);
								PreparedStatement pst=con.prepareStatement("select price from transit_price where airport_id=? and flight_id=?");
								pst.setInt(1, airp.getAirportId());
								pst.setString(2, f.getFlightId());
								ResultSet rs=pst.executeQuery();
								Double d=null;
								while(rs.next()){
									 d=rs.getDouble(1);
								}
								f.setTotalPrice(d);
								f.setLandingAirport(airp.getAirportId()+"");
								rs.close();
								pst.close();
								list.add(f);
								continue;
							}
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
			DBUtils.close(con);
		}
Date d=new Date();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		for(FlightInfo f1:list){
			List<FlightStopTime> li=this.getHibernateTemplate().find(hql3,new Object[]{f1.getFlightId(),sdf.format(d)});
			for(FlightStopTime fs:li){
				Date cd=null;
				Date bd=null;
				Date ed=null;
				try {
					cd = sdf.parse(search.getFirst_start_date());
					bd = sdf.parse(fs.getStopBeginTime());
					ed = sdf.parse(fs.getStopEndTime());
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if((cd.before(ed)&&cd.after(bd))||cd.equals(bd)||cd.equals(ed)){
					list.remove(f1);
				}
			}
			
		}
		List<FlightVO> flist=new ArrayList<FlightVO>();
		for(FlightInfo f2:list){
			Airport a=(Airport) this.getHibernateTemplate().get("com.usth.model.Airport", Integer.parseInt(f2.getTakeOffAirport()));
			Airport b=(Airport) this.getHibernateTemplate().get("com.usth.model.Airport", Integer.parseInt(f2.getLandingAirport()));
			FlightVO f=new FlightVO(f2.getFlightId(), a.getAirportName(), b.getAirportName(), f2.getTakeOffTime(), f2.getLandingTime(), f2.getTotalPrice()+"");
			flist.add(f);
		}
		return flist;
	}

	@Override
	public BuyTVO buy(String date, String flight_id,String landingAirportName) {
		FlightInfo fi=(FlightInfo) this.getHibernateTemplate().get("com.usth.model.FlightInfo", flight_id);
		Airport takeoffair=(Airport) this.getHibernateTemplate().get("com.usth.model.Airport", Integer.parseInt(fi.getTakeOffAirport()));
		List<TransitPrice> tlist=this.getHibernateTemplate().find("from TransitPrice where airportId=? and flightId=?",new Object[]{takeoffair.getAirportId(),fi.getFlightId()});
		List<FlightCom> flist=this.getHibernateTemplate().find("from FlightCom where comId=?",Integer.parseInt(fi.getFlightCompany()));
		String price="";
		TransitPrice tp=new TransitPrice();
		if(tlist!=null&&tlist.size()!=0){
			tp=tlist.get(0);
			price=tp.getPrice()+"";
		}else{
			price=fi.getTotalPrice()+"";
		}
		String hql="from SoldTicket where flightId=?";
		String hql1="from Tickets where flightId=?";
		List li=this.getHibernateTemplate().find(hql,flight_id);
		List<Tickets> list=this.getHibernateTemplate().find(hql1,flight_id);
		Tickets ticket =list.get(0);
		FlightVO fvo=new FlightVO(flight_id, takeoffair.getAirportName(),landingAirportName , fi.getTakeOffTime(), fi.getLandingTime(), price);
		BuyTVO bvo =new BuyTVO();
		bvo.setYp(ticket.getTotalcounts()-li.size());
		bvo.setFvo(fvo);
		bvo.setDate(date);
		bvo.setComName(flist.get(0).getComName());
		return bvo;
	}
	
}
